package com.wanxi.dao.impl;

import com.wanxi.dao.UserDao;
import com.wanxi.model.UserModel;
import com.wanxi.service.UserService;
import com.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    public int add(UserModel model) {
//        调用数据库连接
//        因为数据库连接会执行很多次，也会做增删查改操作，所以应该封装到工具类
        String sql = "INSERT INTO `user` (`username`, `address`, `create_time`, " +
                "`email`, `enable`, `password`, " +
                "`phone`, `sex`, `update_time`," +
                " `hobby`, `birthday`, `remark`) VALUES (" +
                "'" + model.getUsername() + "', '" + model.getAddress() + "', now(), " +
                "'" + model.getEmail() + "', '" + model.getEnable() + "', '" + model.getPassword() + "', " +
                "'" + model.getPhone() + "', '" + model.getSex() + "', sysdate(), " +
                "'" + model.getHobby() + "', '" + model.getBirthday() + "', '" + model.getRemark() + "');";
        return JDBC.update(sql);

    }

    public int delete(UserModel model) {
        String sql = "delete from user where id=" + model.getId();
        return JDBC.update(sql);
    }

    public int update(UserModel model) {
        String sql = "UPDATE `user` SET `username` = '" + model.getUsername() + "', " +
                "`address` = '" + model.getAddress() + "', " +
                "`email` = '" + model.getEmail() + "'," +
                " `password` = '" + model.getPassword() + "', " +
                " `phone` = '" + model.getPhone() + "', " +
                " `sex` = '" + model.getSex() + "'," +
                " `enable` = '" + model.getEnable() + "'," +
                " `update_time` = now()," +
                " `hobby` = '" + model.getHobby() + "', " +
                " `birthday` = '" + model.getBirthday() + "'," +
                " `remark` = '" + model.getRemark() + "'" +
                " WHERE (`id` = '" + model.getId() + "');";
        return JDBC.update(sql);
    }

    public List<UserModel> findAll(UserModel model) {
        int start = (model.getPage() - 1) * model.getLimit();
        String sql = "select *,date_format(birthday ,'%Y/%m/%d') temp from user where true ";
        sql += getSql(model);
        sql += " order by id desc  limit " + start + "," + model.getLimit() + ";";
        List<UserModel> list = new ArrayList<>();
        ResultSet query = JDBC.query(sql);
//        结果集：有可能会有很多条数据，需要对数据进行取出来，遍历，迭代器
        try {
            while (query.next()) {
                UserModel userModel = UserModel.builder()
                        .id(query.getInt("id"))
                        .username(query.getString("username"))
                        .address(query.getString("address"))
                        .createTime(query.getString("create_time"))
                        .email(query.getString("email"))
                        .enable(query.getInt("enable"))
                        .phone(query.getString("phone"))
                        .sex(query.getString("sex"))
                        .updateTime(query.getString("update_time"))
                        .hobby(query.getString("hobby"))
                        .birthday(query.getString("temp"))
                        .remark(query.getString("remark"))
                        .build();
                list.add(userModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int count(UserModel model) {
        String sql = "select count(*) count from user where 1=1";
        sql += getSql(model);
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return query.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    private String getSql(UserModel model) {
        String sql = "";
        if (!"".equals(model.getUsername()) && model.getUsername() != null) {
            sql += " and username like '%" + model.getUsername() + "%' ";
        }
        if (!"".equals(model.getEmail()) && model.getEmail() != null) {
            sql += " and email like '%" + model.getEmail() + "%' ";
        }
        if (model.getEnable() != -1) {
            sql += " and enable=" + model.getEnable();
        }
        if (!"".equals(model.getStart()) && model.getStart() != null) {
            sql += " and birthday >='" + model.getStart() + "'";
        }
        if (!"".equals(model.getEnd()) && model.getEnd() != null) {
            sql += " and birthday <='" + model.getEnd() + "'";
        }
        if (!"".equals(model.getSex()) && model.getSex() != null) {
            sql += " and sex='" + model.getSex() + "'";
        }
        if (!"".equals(model.getHobby()) && model.getHobby() != null) {
            String[] split = model.getHobby().split(",");
            sql += " and (";
            for (int i = 0; i < split.length; i++) {
                if (i == 0) {
                    sql += " find_in_set('" + split[i] + "',hobby) ";
                } else {
                    sql += "  or find_in_set('" + split[i] + "',hobby) ";
                }
            }
            sql += ")";
        }
        return sql;
    }

    public UserModel findById(UserModel model) {
        String sql = "select *,date_format(birthday ,'%Y/%m/%d') temp from user where id=" + model.getId();
        ResultSet query = JDBC.query(sql);
        UserModel userModel = new UserModel();
        try {
            while (query.next()) {
                userModel = UserModel.builder()
                        .id(query.getInt("id"))
                        .username(query.getString("username"))
                        .address(query.getString("address"))
                        .createTime(query.getString("create_time"))
                        .email(query.getString("email"))
                        .enable(query.getInt("enable"))
                        .phone(query.getString("phone"))
                        .sex(query.getString("sex"))
                        .updateTime(query.getString("update_time"))
                        .hobby(query.getString("hobby"))
                        .birthday(query.getString("temp"))
                        .remark(query.getString("remark"))
                        .build();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userModel;
    }

    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select * from user where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
        ResultSet query = JDBC.query(sql);
        UserModel model = null;
        try {
            while (query.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }
}
